package cn.mapper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import cn.pojo.Bill;
import cn.util.DBUtil;
import cn.util.DateUtil;

public class BillMapperImpl extends DBUtil implements BillMapper {

	@Override
	public Integer totalCount(String productName) {
		String sql = "select count(id) from `smbms_bill` where 1=1 ";
		List<Object> list = new ArrayList<Object>();
		if (productName != null && !productName.equals("")) {
			sql += " and productName like ? ;";
			list.add("%" + productName + "%");
		}
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;

	}

	@Override
	public List<Bill> getBillLike(String productName, Integer rowNum, int pagecount) {
		String sql = "select * from `smbms_bill` where 1=1 ";
		List<Object> list = new ArrayList<Object>();
		if (productName != null && !productName.equals("")) {
			sql += " and productName like ? ";
			list.add("%" + productName + "%");
		}
		sql += " limit ?,? ;";
		list.add(rowNum);
		list.add(pagecount);
		System.out.println(sql);
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		List<Bill> list1 = new ArrayList<Bill>();
		try {
			ps = ct.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject((i + 1), list.get(i));
			}
			st = ps.executeQuery();
			while (st.next()) {
				Bill b = new Bill();
				b.setBillCode(st.getString("billCode"));
				b.setCreatedBy(st.getLong("createdBy"));
				b.setCreationDate(st.getTimestamp("creationDate"));
				b.setId(st.getLong("id"));
				b.setIsPayment(st.getInt("isPayment"));
				b.setModifyBy(st.getLong("modifyBy"));
				b.setModifyDate(st.getTimestamp("modifyDate"));
				b.setProductCount(st.getDouble("productCount"));
				b.setProductDesc(st.getString("productDesc"));
				b.setProductName(st.getString("productName"));
				b.setProductUnit(st.getString("productUnit"));
				b.setProviderId(st.getInt("providerId"));
				b.setTotalPrice(st.getDouble("totalPrice"));
				list1.add(b);
			}
			return list1;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;

	}

	// @Test
	// public void t() {
	// for (Bill b : getBillLike(null, 1, 5)) {
	// System.out.println(b);
	// }
	//
	// }

	@Override
	public Integer[] deleteBill(String[] id) {
		String sql = " delete from smbms_bill where id in ( ";
		for (int i = 0; i < id.length; i++) {
			if (i < id.length - 1) {
				sql += id[i] + ",";
			} else {
				sql += id[i];
			}
		}
		sql += " );";
		Integer[] num = cud(Bill.class, sql);
		return num;
	}

	@Override
	public int billRemove(String id) {
		String sql = " delete from smbms_bill where id =?  ";
		Integer[] num = cud(Bill.class, sql, id);
		if (num.length > 0) {
			return num[0];
		}
		return 0;
	}

	@Override
	public List<Bill> getBillsByIdArray(String[] id) {
		String sql = " select * from smbms_bill where id in ( ";
		List<Bill> list = new ArrayList<Bill>();
		if (id == null) {
			return list;
		}
		if (id.length == 0) {
			return list;
		}
		for (int i = 0; i < id.length; i++) {
			if (i < id.length - 1) {
				sql += id[i] + ",";
			} else {
				sql += id[i];
			}
		}
		sql += " );";
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			st = ps.executeQuery();
			while (st.next()) {
				Bill b = new Bill();
				b.setBillCode(st.getString("billCode"));
				b.setCreatedBy(st.getLong("createdBy"));
				b.setCreationDate(st.getTimestamp("creationDate"));
				b.setId(st.getLong("id"));
				b.setIsPayment(st.getInt("isPayment"));
				b.setModifyBy(st.getLong("modifyBy"));
				b.setModifyDate(st.getTimestamp("modifyDate"));
				b.setProductCount(st.getDouble("productCount"));
				b.setProductDesc(st.getString("productDesc"));
				b.setProductName(st.getString("productName"));
				b.setProductUnit(st.getString("productUnit"));
				b.setProviderId(st.getInt("providerId"));
				b.setTotalPrice(st.getDouble("totalPrice"));
				list.add(b);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public Bill getBillById(Integer id) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = " select * from smbms_bill where id=? ; ";
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, id);
			st = ps.executeQuery();
			while (st.next()) {
				Bill b = new Bill();
				b.setBillCode(st.getString("billCode"));
				b.setCreatedBy(st.getLong("createdBy"));
				b.setCreationDate(st.getTimestamp("creationDate"));
				b.setId(st.getLong("id"));
				b.setIsPayment(st.getInt("isPayment"));
				b.setModifyBy(st.getLong("modifyBy"));
				b.setModifyDate(st.getTimestamp("modifyDate"));
				b.setProductCount(st.getDouble("productCount"));
				b.setProductDesc(st.getString("productDesc"));
				b.setProductName(st.getString("productName"));
				b.setProductUnit(st.getString("productUnit"));
				b.setProviderId(st.getInt("providerId"));
				b.setTotalPrice(st.getDouble("totalPrice"));
				return b;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	@Override
	public int updateBillById(Bill bill) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		String sql = " update smbms_bill set productName=?, productDesc=?, productUnit=?, productCount=?, totalPrice=?, isPayment=?, createdBy=?, creationDate=?, providerId=? where id=?; ";
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, bill.getProductName());
			ps.setObject(2, bill.getProductDesc());
			ps.setObject(3, bill.getProductUnit());
			ps.setObject(4, bill.getProductCount());
			ps.setObject(5, bill.getTotalPrice());
			ps.setObject(6, bill.getIsPayment());
			ps.setObject(7, bill.getCreatedBy());
			ps.setObject(8, bill.getCreationDate());
			ps.setObject(9, bill.getProviderId());
			ps.setObject(10, bill.getId());
			int i = ps.executeUpdate();
			ct.commit();
			return i;
		} catch (Exception e) {
			try {
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			release(ct, ps, null);
		}
		return 0;
	}

	@Override
	public int addBill(Bill bill) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		Timestamp date = DateUtil.convertUtilDateToTimeStamp(new Date());
		String sql = " insert into smbms_bill (billCode, productName, productDesc, productUnit, productCount, totalPrice, isPayment, createdBy, creationDate, providerId) values(?,?,?,?,?,?,?,?,?,?) ";
		try {
			ps = ct.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setObject(1, bill.getBillCode());
			ps.setObject(2, bill.getProductName());
			ps.setObject(3, bill.getProductDesc());
			ps.setObject(4, bill.getProductUnit());
			ps.setObject(5, bill.getProductCount());
			ps.setObject(6, bill.getTotalPrice());
			ps.setObject(7, bill.getIsPayment());
			ps.setObject(8, bill.getCreatedBy());
			ps.setObject(9, date);
			ps.setObject(10, bill.getProviderId());
			int i = ps.executeUpdate();
			st = ps.getGeneratedKeys();
			ct.commit();
			while (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			try {
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			release(ct, ps, null);
		}
		return 0;
	}

	@Override
	public Bill getBillByBillCode(String billCode) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		String sql = " select * from smbms_bill where billCode=? ; ";
		try {
			ps = ct.prepareStatement(sql);
			ps.setObject(1, billCode);
			st = ps.executeQuery();
			while (st.next()) {
				Bill b = new Bill();
				b.setBillCode(st.getString("billCode"));
				b.setCreatedBy(st.getLong("createdBy"));
				b.setCreationDate(st.getTimestamp("creationDate"));
				b.setId(st.getLong("id"));
				b.setIsPayment(st.getInt("isPayment"));
				b.setModifyBy(st.getLong("modifyBy"));
				b.setModifyDate(st.getTimestamp("modifyDate"));
				b.setProductCount(st.getDouble("productCount"));
				b.setProductDesc(st.getString("productDesc"));
				b.setProductName(st.getString("productName"));
				b.setProductUnit(st.getString("productUnit"));
				b.setProviderId(st.getInt("providerId"));
				b.setTotalPrice(st.getDouble("totalPrice"));
				return b;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;

	}

	// @Test
	// public void t1() {
	// Bill bill = new Bill();
	// bill.setId((long) 4);
	// bill.setCreatedBy((long) 1);
	// bill.setCreationDate(DateUtil.convertUtilDateToTimeStamp(DateUtil.convertStringToUtilDate("1998-08-30")));
	// bill.setIsPayment(1);
	// bill.setProductCount(200.00);
	// bill.setProductDesc("食品-进口食用油");
	// bill.setProductName("橄榄油");
	// bill.setProductUnit("斤");
	// bill.setProviderId(1);
	// bill.setTotalPrice(500.00);
	// System.out.println(updateBillById(bill));
	// }
}
